﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

namespace bzxxxt
{
    public partial class map : System.Web.UI.Page
    {
        private static string newString = System.Configuration.ConfigurationManager.ConnectionStrings["newconn"].ConnectionString;
        private static string oldString = System.Configuration.ConfigurationManager.ConnectionStrings["olddbconn"].ConnectionString;
        DataTable dtMap = new DataTable();
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                ddlDataBind();
                gvDataBind(ddlPname.Text);
            }
            else
            {
                //gvDataBind(ddlPname.Text);
            }

        }

        protected void ddlPname_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (ddlPname.SelectedIndex < 0) return;
            gvDataBind(ddlPname.Text);
        }

        protected void btnChange_Click(object sender, EventArgs e)
        {
            string oldpname = ddlPname.Text;
            string newname = txtname.Text;
            string sql = string.Format("update tbMap set pname='{0}' where pname='{1}'", newname, oldpname);
            int i = execSQL(sql);
            ddlDataBind();
            gvDataBind(newname);
        }

        protected void gvMap_RowEditing(object sender, GridViewEditEventArgs e)
        {
            gvMap.EditIndex = e.NewEditIndex;
            gvDataBind(ddlPname.Text);

        }

        protected void ddlDataBind()
        {
            String sql = "select distinct(pname) as pname from tbMap";
            SqlDataAdapter da = new SqlDataAdapter(sql, newString);
            DataTable dt = new DataTable();
            da.Fill(dt);
            ddlPname.Items.Clear();
            ddlPname.DataSource = dt;
            ddlPname.DataTextField = "pname";
            ddlPname.DataBind();
        }

        protected void gvDataBind(string pname)
        {

            String sql = "select * from tbMap order by pname, ttype,oldsection";
            if (!string.IsNullOrEmpty(pname))
            {
                sql = "select * from tbMap where pname='" + pname + "' order by pname, ttype,oldsection";
            }
            SqlDataAdapter da = new SqlDataAdapter(sql, newString);
            dtMap.Rows.Clear();
            da.Fill(dtMap);
            gvMap.DataSource = dtMap;
            gvMap.DataBind();
        }

        private int execSQL(string sql)
        {
            SqlConnection conn = new SqlConnection(newString);
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);

            int i = cmd.ExecuteNonQuery();
            conn.Close();
            return i;
        }
        protected void gvMap_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            string id = e.Keys["id"].ToString();
            string newnum = e.NewValues["newnum"].ToString();
            string sql = string.Format("update tbMap set newnum='{0}' where id={1}", newnum, id);
            try
            {
                SqlConnection conn = new SqlConnection(newString);
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);

                int i = cmd.ExecuteNonQuery();
                //if (i > 0)
                //{
                //    ClientScript.RegisterStartupScript(ClientScript.GetType(), "myscript", "<script language='javascript'>alert('修改成功！')</script>");
                //}
                //else
                //{
                //    ClientScript.RegisterStartupScript(ClientScript.GetType(), "myscript", "<script language='javascript'>alert('修改失败！')</script>");
                //}
            }
            catch (Exception ex)
            {
                ClientScript.RegisterStartupScript(ClientScript.GetType(), "myscript", "<script language='javascript'>alert('操作无效！')</script>");
            }
            gvMap.EditIndex = -1;
            gvDataBind(ddlPname.Text);
        }

        protected void gvMap_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
        {
            e.Cancel = true;
            gvMap.EditIndex = -1;
            gvDataBind(ddlPname.Text);
        }

        protected void gvMap_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {

            string id = e.Keys["id"].ToString();
            string sql = string.Format("delete from tbMap where id={0}", id);
            try
            {
                SqlConnection conn = new SqlConnection(newString);
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);

                int i = cmd.ExecuteNonQuery();
                //if (i > 0)
                //{
                //    ClientScript.RegisterStartupScript(ClientScript.GetType(), "myscript", "<script language='javascript'>alert('删除成功！')</script>");
                //}
                //else
                //{
                //    ClientScript.RegisterStartupScript(ClientScript.GetType(), "myscript", "<script language='javascript'>alert('删除失败！')</script>");
                //}
            }
            catch (Exception ex)
            {
                ClientScript.RegisterStartupScript(ClientScript.GetType(), "myscript", "<script language='javascript'>alert('操作无效！')</script>");
            }
            gvMap.EditIndex = -1;
            gvDataBind(ddlPname.Text);
        }

        protected void btnInit_Click(object sender, EventArgs e)
        {            
            InitMap();
            ddlDataBind();
            gvDataBind(ddlPname.Text);
        }
        public void InitMap()
        {
            string sql = "truncate table tbMap";
            execSQL(sql);

            SqlConnection conn = new SqlConnection(newString);
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            sql = "select distinct rtrim(ltrim(骨灰堂名)) as pname  from lianxi";
            SqlDataAdapter da = new SqlDataAdapter(sql, oldString);
            DataTable dt = new DataTable();
            da.Fill(dt);

            string[] exs = { "4" };

            foreach (DataRow dr in dt.Rows)
            {
                string pname = dr["pname"].ToString().Trim();
                //if(pname=="孝安楼") continue;
                //if (pname == "塘市骨灰堂") continue;
                sql = "select distinct rtrim(ltrim(区)) as section  from lianxi where 骨灰堂名='" + pname + "'";
                da.SelectCommand.CommandText = sql;
                DataTable dtSec = new DataTable();
                da.Fill(dtSec);
                int cnt = 1;
                string insql = "";
                foreach (DataRow dr2 in dtSec.Rows)
                {
                    if (exs.Any(m => (cnt + "").IndexOf(m + "") >= 0)) cnt++;
                    string num = string.Format("{0:00}", cnt++);
                    string secname = dr2["section"].ToString();
                    insql += string.Format("insert tbMap(oldsection,oldline,newnum,ttype,pname) values('{0}',null,'{1}',1,'{2}')", secname, num, dr["pname"]) + ";";
                    sql = "select distinct rtrim(ltrim(排)) as line from lianxi where 骨灰堂名='" + pname + "' and 区='" + secname + "'";
                    da.SelectCommand.CommandText = sql;
                    DataTable dtline = new DataTable();
                    da.Fill(dtline);
                    int cnt2 = 1;

                    foreach (DataRow dr3 in dtline.Rows)
                    {
                        string linename = dr3["line"].ToString();
                        if (exs.Any(m => (cnt2 + "").IndexOf(m + "") >= 0)) cnt2++;
                        string num2 = string.Format("{0:00}", cnt2++);
                        insql += string.Format("insert tbMap(oldsection,oldline,newnum,ttype,pname) values('{0}','{1}','{2}',2,'{3}')", secname, linename, num2, dr["pname"]) + ";";
                    }
                    //cmd.CommandText = insql;
                    //cmd.ExecuteNonQuery();      
                }
                cmd.CommandText = insql;
                cmd.ExecuteNonQuery();
            }            
        }

        protected void btnExport_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            string sql = "select id, pname as 骨灰堂, oldsection as 区名, oldline as 排,CASE ttype WHEN 1 THEN '区' WHEN 2 THEN '排' end as 类型, newnum as 新编号 from tbMap order by pname, ttype,oldsection";
            SqlDataAdapter da = new SqlDataAdapter(sql, newString);
            da.Fill(dt);
            string filename = "/admin/upload/映射表.xls";
            string filepath = Server.MapPath(filename);
            admin.service.NPOIHelper.ExportDTtoExcel(dt, "映射表",filepath);
            linkFile.NavigateUrl = filename;
            linkFile.Text = filename;
        }
    }
}